package com.lidong.zc.databasemodel.init.mysql;

import com.lidong.zc.databasemodel.constants.BaseConst;
import com.lidong.zc.databasemodel.constants.enumconst.SqlTypeEnum;
import com.lidong.zc.databasemodel.constants.enumconst.StrCaseEnum;
import com.lidong.zc.databasemodel.constants.database.CharacterSetEnum;
import com.lidong.zc.databasemodel.constants.database.EngineEnum;
import com.lidong.zc.databasemodel.constants.database.SqlColumnEnum;
import com.lidong.zc.databasemodel.constants.database.TableOrderEnum;
import com.lidong.zc.databasemodel.entity.database.DataBaseColumn;
import com.lidong.zc.databasemodel.entity.database.DataBaseForeignKey;
import com.lidong.zc.databasemodel.entity.database.DataBaseIndex;
import com.lidong.zc.databasemodel.entity.database.DataBasePrimaryKey;
import com.lidong.zc.databasemodel.entity.database.DataBaseTable;
import com.lidong.zc.databasemodel.entity.database.DataBaseUniqueKey;
import com.lidong.zc.databasemodel.init.intf.CreateTableHandle;
import com.lidong.zc.databasemodel.init.core.DataBaseMetaDataHandle;
import com.lidong.zc.databasemodel.init.core.InitDataBaseContext;
import com.lidong.zc.databasemodel.utils.CreateTableUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author LiDong
 * @version 1.0.0
 * @createTime 9/17/2022 9:26 PM
 */
public class MysqlCreateTableHandle extends DataBaseMetaDataHandle implements CreateTableHandle {

    private static final Logger logger = LoggerFactory.getLogger(MysqlCreateTableHandle.class);

    public MysqlCreateTableHandle(InitDataBaseContext initDataBaseContext) {
        super(initDataBaseContext);
    }

    @Override
    public void startCreateTable(List<DataBaseTable> needCreateTableList) throws SQLException {
        // 获取所用新增表的建表结构
        List<String> createTableList = this.getCreateTableSqlList(needCreateTableList);
        logger.info("需要创建：{} 张表", createTableList.size());
        for (String createTableSql : createTableList) {
            try {
                jdbcTemplate.update(createTableSql);
            } catch (Exception e) {
                logger.error(createTableSql);
                logger.error(e.getMessage(), e);
            }
        }
    }

    /**
     * 更新表
     *
     * @param needUpdateTableList
     * @param oldTableList
     */
    @Override
    public void startUpdateTable(List<DataBaseTable> needUpdateTableList, List<DataBaseTable> oldTableList) {
        List<String> updateSqlList = new ArrayList<>();
        for (DataBaseTable dataBaseTable : needUpdateTableList) {
            // 对比增加的字段
            List<DataBaseColumn> columnList = dataBaseTable.getColumnList().stream().filter(x -> !ObjectUtils.isEmpty(x.getName())).collect(Collectors.toList());
            DataBaseTable metaDataTable = oldTableList.stream().filter(x -> x.getTableName().equalsIgnoreCase(dataBaseTable.getTableName())).findAny().orElse(null);
            if (Objects.isNull(metaDataTable)) {
                continue;
            }
            String tableSchema = metaDataTable.getTableSchema();
            String tableName = metaDataTable.getTableName();
            List<DataBaseColumn> metaDataColumnList = metaDataTable.getColumnList();
            for (DataBaseColumn dataBaseColumn : columnList) {
                boolean flag = false;
                String columnName = CreateTableUtils.getColumnName(dataBaseColumn);
                for (DataBaseColumn metaDataDataBaseColumn : metaDataColumnList) {
                    String metaDataColumnName = metaDataDataBaseColumn.getName();
                    if (columnName.equalsIgnoreCase(metaDataColumnName)) {
                        flag = true;
                        break;
                    }
                }
                if (!flag) {
                    SqlColumnEnum dataType = dataBaseColumn.getDataType();
                    int dataTypeLength = dataBaseColumn.getLength();
                    Object defaultValue = dataBaseColumn.getDefaultValue();
                    int decimalLength = dataBaseColumn.getDecimalLength();
                    String remark = dataBaseColumn.getRemark();
                    boolean notNull = dataBaseColumn.isNotNull();
                    boolean autoIncrement = dataBaseColumn.isAutoIncrement();
                    boolean isPrimaryKey = dataBaseColumn.isPrimaryKey();
                    DataBasePrimaryKey primaryKey = metaDataTable.getPrimaryKey();
                    List<DataBaseColumn> primaryKeyColumns = primaryKey.getColumns();
                    // 已经有主键但是当前字段标为主键当前字段不做处理
                    if (isPrimaryKey && !ObjectUtils.isEmpty(primaryKeyColumns)) {
                        continue;
                    }
                    String columnTypeSql = CreateTableUtils.getColumnTypeSql(dataType, dataTypeLength, decimalLength);
                    String otherSql = CreateTableUtils.getOtherSql(notNull, dataType, autoIncrement, isPrimaryKey, defaultValue, remark);
                    // 组装字段的 sql
                    String sql = "ALTER TABLE " + tableSchema + BaseConst.POINT + tableName + " ADD `" + columnName + "` ";
                    sql += columnTypeSql;
                    sql += otherSql;
                    updateSqlList.add(sql);
                }
            }
        }
        logger.info("需要更新 sql {} 条", updateSqlList.size());
        for (String createTableSql : updateSqlList) {
            jdbcTemplate.update(createTableSql);
        }
    }


    /**
     * 组装建表 sql
     *
     * @param needCreateTableList 需要新建的表
     * @return List
     */
    private List<String> getCreateTableSqlList(List<DataBaseTable> needCreateTableList) {
        List<String> sqlList = new ArrayList<>();
        InitDataBaseContext initDataBaseContext = this.getInitDataBaseContext();
        SqlTypeEnum sqlType = initDataBaseContext.getSqlType();
        for (DataBaseTable dataBaseTable : needCreateTableList) {
            String tableName = dataBaseTable.getTableName();
            EngineEnum tableEngine = dataBaseTable.getTableEngine();
            StrCaseEnum tableNameCase = dataBaseTable.getTableNameCase();
            int autoIncrementNum = dataBaseTable.getAutoIncrementNum();
            CharacterSetEnum character = dataBaseTable.getCharacter();
            TableOrderEnum orderType = dataBaseTable.getOrderType();
            String remark = dataBaseTable.getRemark();
            List<DataBaseColumn> columnList = Optional.ofNullable(dataBaseTable.getColumnList()).orElse(Collections.emptyList());
            DataBasePrimaryKey dataBasePrimaryKey = dataBaseTable.getDataBasePrimaryKey();
            List<DataBaseForeignKey> foreignKeyList = Optional.ofNullable(dataBaseTable.getForeignKeyList()).orElse(Collections.emptyList());
            List<DataBaseIndex> indexList = Optional.ofNullable(dataBaseTable.getIndexList()).orElse(Collections.emptyList());
            List<DataBaseUniqueKey> uniqueKeyList = Optional.ofNullable(dataBaseTable.getUniqueKeyList()).orElse(Collections.emptyList());
            String nameTimeSuffix = dataBaseTable.getNameTimeSuffix();
            DataBasePrimaryKey primaryKey = dataBaseTable.getPrimaryKey();

            if (ObjectUtils.isEmpty(tableName) || ObjectUtils.isEmpty(columnList)) {
                continue;
            }
            StringBuilder sqlStringBuilder = new StringBuilder();
            if (StrCaseEnum.UPPER == tableNameCase) {
                tableName = tableName.toUpperCase(Locale.ROOT);
            } else {
                tableName = tableName.toLowerCase(Locale.ROOT);
            }
            sqlStringBuilder.append("CREATE TABLE ").append("`").append(tableName).append("` ");
            sqlStringBuilder.append(BaseConst.LEFT_BRACKET);
            // 字段拼接
            String columnSql = this.getMysqlColumnSql(columnList, primaryKey);
            String columnIndexSql = this.getMysqlColumnIndexSql(dataBasePrimaryKey, foreignKeyList, indexList, uniqueKeyList);
            sqlStringBuilder.append(columnSql);
            sqlStringBuilder.append(columnIndexSql);
            sqlStringBuilder.append(BaseConst.RIGHT_BRACKET);
            sqlStringBuilder.append(BaseConst.BLANK);
            sqlStringBuilder.append("ENGINE=").append(tableEngine.getCode());
            sqlStringBuilder.append(BaseConst.BLANK);
            String tableCharset = this.getTableCharset(character);
            sqlStringBuilder.append("CHARSET=").append(tableCharset);
            if (!ObjectUtils.isEmpty(remark)) {
                sqlStringBuilder.append(" COMMENT='").append(remark).append("'");
            }
            sqlStringBuilder.append(BaseConst.SEMICOLON);
            sqlList.add(sqlStringBuilder.toString());
        }
        return sqlList;
    }

    /**
     * 获取索引 sql
     *
     * @param dataBasePrimaryKey 主键
     * @param foreignKeyList     外键
     * @param indexList          索引
     * @param uniqueKeyList      唯一索引
     * @return String
     */
    private String getMysqlColumnIndexSql(DataBasePrimaryKey dataBasePrimaryKey, List<DataBaseForeignKey> foreignKeyList, List<DataBaseIndex> indexList, List<DataBaseUniqueKey> uniqueKeyList) {
        StringBuilder sql = new StringBuilder(BaseConst.BLANK + "");
        // 拼接主键
        if (Objects.nonNull(dataBasePrimaryKey)) {
            String primaryKeyName = Optional.ofNullable(dataBasePrimaryKey.getName()).orElse("");
            List<DataBaseColumn> primaryKeyColumns = Optional.ofNullable(dataBasePrimaryKey.getColumns()).orElse(Collections.emptyList());
            primaryKeyColumns = primaryKeyColumns.stream().filter(column -> !ObjectUtils.isEmpty(column.getName())).collect(Collectors.toList());
            StringBuilder tempStr = new StringBuilder();
            for (DataBaseColumn primaryKeyColumn : primaryKeyColumns) {
                String name = this.getColumnName(primaryKeyColumn);
                tempStr.append(BaseConst.SYMBOL_ONE).append(name).append(BaseConst.SYMBOL_ONE).append(BaseConst.COMMA);
            }
            if (tempStr.toString().endsWith(BaseConst.COMMA)) {
                tempStr = new StringBuilder(tempStr.substring(0, tempStr.length() - 1));
            }
            if (!ObjectUtils.isEmpty(tempStr)) {
                sql.append(BaseConst.BLANK + "PRIMARY KEY (").append(tempStr).append("),");
            }
        }
        // 拼接 unique key
        for (DataBaseUniqueKey dataBaseUniqueKey : uniqueKeyList) {
            String indexSql = "UNIQUE KEY ";
            String indexName = dataBaseUniqueKey.getName();
            indexSql += BaseConst.SYMBOL_ONE + indexName + BaseConst.SYMBOL_ONE;
            StringBuilder keySql = new StringBuilder(BaseConst.LEFT_BRACKET);
            List<DataBaseColumn> columns = dataBaseUniqueKey.getColumns();
            if (ObjectUtils.isEmpty(columns)) {
                continue;
            }
            for (DataBaseColumn column : columns) {
                String name = this.getColumnName(column);
                keySql.append(BaseConst.SYMBOL_ONE).append(name).append(BaseConst.COMMA).append(BaseConst.SYMBOL_ONE);
            }
            keySql.append(BaseConst.RIGHT_BRACKET);
            keySql.append(" USING BTREE" + BaseConst.COMMA);
            indexSql += keySql;
            sql.append(indexSql);
        }
        // 拼接 index 索引
        for (DataBaseIndex dataBaseIndex : indexList) {
            String indexSql = "KEY ";
            String indexName = dataBaseIndex.getName();
            indexSql += BaseConst.SYMBOL_ONE + indexName + BaseConst.SYMBOL_ONE + BaseConst.BLANK;
            StringBuilder keySql = new StringBuilder(BaseConst.LEFT_BRACKET);
            List<DataBaseColumn> columns = dataBaseIndex.getColumns();
            StringBuilder tempSql = new StringBuilder();
            for (DataBaseColumn column : columns) {
                String name = this.getColumnName(column);
                tempSql.append(BaseConst.SYMBOL_ONE).append(name).append(BaseConst.SYMBOL_ONE).append(BaseConst.COMMA);
            }
            if (tempSql.toString().endsWith(BaseConst.COMMA)) {
                keySql.append(tempSql.substring(0, tempSql.length() - 1));
            }
            keySql.append(BaseConst.RIGHT_BRACKET);
            keySql.append(" USING BTREE" + BaseConst.COMMA);
            indexSql += keySql;
            sql.append(indexSql);
        }
        if (sql.toString().endsWith(BaseConst.COMMA)) {
            return sql.substring(0, sql.length() - 1);
        }
        return sql.toString();
    }

    /**
     * 获取 sql 字段部分
     *
     * @param columnList         字段
     * @param dataBasePrimaryKey 表主键
     * @return
     */
    private String getMysqlColumnSql(List<DataBaseColumn> columnList, DataBasePrimaryKey dataBasePrimaryKey) {
        StringBuilder columnSql = new StringBuilder();
        for (DataBaseColumn dataBaseColumn : columnList) {
            String columnName = this.getColumnName(dataBaseColumn);
            SqlColumnEnum dataType = dataBaseColumn.getDataType();
            int length = dataBaseColumn.getLength();
            int decimalLength = dataBaseColumn.getDecimalLength();
            String remark = dataBaseColumn.getRemark();
            CharacterSetEnum character = dataBaseColumn.getCharacter();
            // 是否主键
            boolean isPrimaryKey = dataBaseColumn.isPrimaryKey();
            if (Objects.nonNull(dataBasePrimaryKey)) {
                List<DataBaseColumn> primaryKeyColumns = Optional.ofNullable(dataBasePrimaryKey.getColumns()).orElse(Collections.emptyList());
                if (1 == primaryKeyColumns.size()) {
                    String primaryKeyColumnName = primaryKeyColumns.get(0).getName();
                    if (columnName.equalsIgnoreCase(primaryKeyColumnName)) {
                        isPrimaryKey = true;
                    }
                }
            }
            boolean notNull = dataBaseColumn.isNotNull();
            boolean autoIncrement = dataBaseColumn.isAutoIncrement();
            Object defaultValue = dataBaseColumn.getDefaultValue();
            Integer dataTypeLength = dataType.getLength();
            Integer decimalPoint = dataType.getDecimalPoint();
            Integer defaultLength = dataType.getDefaultLength();
            if (0 < length) {
                dataTypeLength = length;
            }
            if (0 < decimalLength) {
                decimalPoint = decimalLength;
            }
            if (0 == dataTypeLength) {
                dataTypeLength = defaultLength;
            }
            String columnNameSql = BaseConst.SYMBOL_ONE + columnName + BaseConst.SYMBOL_ONE + BaseConst.BLANK;
            String columnTypeSql = CreateTableUtils.getColumnTypeSql(dataType, dataTypeLength, decimalPoint);
            String otherSql = CreateTableUtils.getOtherSql(notNull, dataType, autoIncrement, isPrimaryKey, defaultValue, remark);
            columnSql.append(columnNameSql).append(columnTypeSql).append(otherSql).append(BaseConst.COMMA);
        }
        return columnSql.toString();
    }


}
